﻿using Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    /// <summary>
    /// 疫情上报
    /// </summary>
    public class ReportOnEpidemicSituationDal
    {
        DbManager db = new DbManager();

        /// <summary>
        /// 根据reader获取数据集合
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        List<ReportOnEpidemicSituationModel> GetDataListByDataReader(SqlDataReader reader)
        {
            List<ReportOnEpidemicSituationModel> thmLi = new List<ReportOnEpidemicSituationModel>();
            while (reader.Read())
            {
                ReportOnEpidemicSituationModel thm = new ReportOnEpidemicSituationModel();
                thm.Id = Guid.Parse(reader["Id"].ToString());
                thm.DepartId = reader["DepartId"].ToString();
                thm.DepartName = reader["DepartName"].ToString();
                thm.userId = reader["userId"].ToString();
                thm.userName = reader["userName"].ToString();
                thm.FillInDate = DateTime.Parse(reader["FillInDate"].ToString());
                thm.FillInNum = int.Parse(reader["FillInNum"].ToString());
                thm.AddDiagnosisNum = int.Parse(reader["AddDiagnosisNum"].ToString());
                thm.AddSuspectedNum = int.Parse(reader["AddSuspectedNum"].ToString());
                thm.AddCureNum = int.Parse(reader["AddCureNum"].ToString());
                thmLi.Add(thm);
            }
            reader.Close();
            reader.Dispose();
            return thmLi;
        }

        /// <summary>
        /// 根据dataReader获取单条数据
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        ReportOnEpidemicSituationModel GetSingleDataByDataReader(SqlDataReader reader)
        {
            ReportOnEpidemicSituationModel thm = new ReportOnEpidemicSituationModel();
            while (reader.Read())
            {
                thm.Id = Guid.Parse(reader["Id"].ToString());
                thm.DepartId = reader["DepartId"].ToString();
                thm.DepartName = reader["DepartName"].ToString();
                thm.userId = reader["userId"].ToString();
                thm.userName = reader["userName"].ToString();
                thm.FillInDate = DateTime.Parse(reader["FillInDate"].ToString());
                thm.FillInNum = int.Parse(reader["FillInNum"].ToString());
                thm.AddDiagnosisNum = int.Parse(reader["AddDiagnosisNum"].ToString());
                thm.AddSuspectedNum = int.Parse(reader["AddSuspectedNum"].ToString());
                thm.AddCureNum = int.Parse(reader["AddCureNum"].ToString());
            }
            reader.Close();
            reader.Dispose();
            return thm;
        }

        /// <summary>
        /// 根据id获取单条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ReportOnEpidemicSituationModel GetSingleDataById(string id)
        {
            string sql = "select * from ReportOnEpidemicSituation where cast(Id as varchar(36)) ='" + id + "'";
            SqlDataReader reader = db.RunDataReaderResultSql(sql);
            return GetSingleDataByDataReader(reader);
        }

        /// <summary>
        /// 疫情上报分页列表
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="projectName"></param>
        /// <param name="FillInDate"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<ReportOnEpidemicSituationModel> GetDataList(string departId, string userName, string projectName, string FillInDateStart, string FillInDateEnd, int pageSize, int pageIndex, out int total)
        {
            if (string.IsNullOrEmpty(departId))
            {
                total = 0;
                List<ReportOnEpidemicSituationModel> a = new List<ReportOnEpidemicSituationModel>();
                return a;
            }
            string where = "";
            if (!string.IsNullOrEmpty(projectName))
            {
                where += " and DepartName like '%" + projectName + "%'";
            }
            if (!string.IsNullOrEmpty(userName))
            {
                where += " and userName like '%" + userName + "%'";
            }
            if (!string.IsNullOrEmpty(FillInDateStart))
            {
                where += " and FillInDate >= '" + DateTime.Parse(FillInDateStart) + "'";
            }
            if (!string.IsNullOrEmpty(FillInDateEnd))
            {
                where += " and FillInDate <= '" + DateTime.Parse(FillInDateEnd) + "'";
            }
            string sql = "with query as (";
            sql += " select UnitId from Unit where UnitId='" + departId + "')";
            //sql += " union all select b.UnitId from query a ";
            //sql += " inner join Unit as b";
            //sql += " on a.UnitId=b.ParentId )";
            string sqlCount = "";
            sqlCount = sql + " select COUNT(1) from ReportOnEpidemicSituation where exists(select 1 from query where query.UnitId=ReportOnEpidemicSituation.DepartId )";
            sqlCount += where;

            sql += " select * from (";
            sql += " select ROW_NUMBER() over(order by FillInDate desc) num, * from ReportOnEpidemicSituation where exists(select 1 from query where query.UnitId=ReportOnEpidemicSituation.DepartId )";
            sql += where + " ) as c where c.num between " + (pageSize * (pageIndex - 1) + 1) + " and  " + pageSize * pageIndex;
            total = int.Parse(db.GetFirstData(sqlCount).ToString());
            SqlDataReader reader = db.RunDataReaderResultSql(sql);
            return GetDataListByDataReader(reader);
        }

        /// <summary>
        /// 体温上报
        /// </summary>
        /// <param name="thm"></param>
        /// <returns></returns>
        public bool InsertReport(ReportOnEpidemicSituationModel thm)
        {
            //疫情汇总
            //查询汇总表是否已汇总当天数据 已汇总就只修改上报人数 未汇总则先保存一条当天数据
            string sql = "";
            string sqlCount = "SELECT COUNT(*) FROM ReportOnEpidemicSituation where FillInDate='" + thm.FillInDate.ToString("yyyy-MM-dd") + "' and DepartId = '" + thm.DepartId + "'";
            int count = int.Parse(db.GetFirstData(sqlCount).ToString());
            if (count > 0)
            {
                //当天已汇总
                sql += " UPDATE ReportOnEpidemicSituation SET FillInNum=FillInNum+" + thm.FillInNum + ",AddSuspectedNum=AddSuspectedNum+" + thm.AddSuspectedNum + " where FillInDate= '" + thm.FillInDate.ToString("yyyy-MM-dd") + "' and DepartId = '" + thm.DepartId + "'";
            }
            else
            {
                //当天未汇总
                sql += " INSERT INTO ReportOnEpidemicSituation(Id,userName,userId,DepartId,DepartName,FillInDate,FillInNum,AddSuspectedNum)" +
                    "VALUES('" + Guid.NewGuid() + "','" + thm.userName + "','" + thm.userId + "','" + thm.DepartId + "','" + thm.DepartName + "','" + thm.FillInDate.ToString("yyyy-MM-dd") + "'," + thm.FillInNum + "," + thm.AddSuspectedNum + ");";
            }
            try
            {
                db.RunNoneResultSql(sql);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 计算上报人数
        /// </summary>
        /// <returns></returns>
        public int GetFillInNum(DateTime FillInDate, TwtzModel entity) {
            int n = 0;
            string sql = "select DISTINCT TemperaturePersonId from TemperaturePerson where TwtzId in(select twtzid from twtz where Convert(varchar, Operatetime, 120) like '" + FillInDate.ToString("yyyy-MM-dd") + "%')";
            DataTable dt = db.RunDataTableResultSql(sql);
            if (dt.Rows.Count == 0) {
                return entity.TemperaturePerson.Count();
            }
            string columnName = "TemperaturePersonId";
            foreach (var item in entity.TemperaturePerson)
            {
                DataRow[] dataRows = dt.Select(columnName + "='" + item.TemperaturePersonId + "'");
                if (dataRows.Length.Equals(1))
                {
                    //存在不记录上报人数
                }
                else
                {
                    n = n + 1;
                }
            }
            return n;
        }

        /// <summary>
        /// 计算疑似人数
        /// </summary>
        /// <returns></returns>
        public int GetSuspectedNum(DateTime FillInDate, TwtzModel entity)
        {
            int n = 0;
            int SuspectedNum = 0;
            foreach (var item in entity.TemperaturePerson)
            {
                if ((double)item.Temperature > 37.2)
                {
                    SuspectedNum += 1;
                }
            }
            string sql = "select DISTINCT TemperaturePersonId from TemperaturePerson where TwtzId in(select twtzid from twtz where Convert(varchar, Operatetime, 120) like '" + FillInDate.ToString("yyyy-MM-dd") + "%') and Temperature >37.2";
            DataTable dt = db.RunDataTableResultSql(sql);
            if (dt.Rows.Count == 0)
            {
                return SuspectedNum;
            }
            string columnName = "TemperaturePersonId";
            foreach (var item in entity.TemperaturePerson)
            {
                if ((double)item.Temperature > 37.2) {
                    DataRow[] dataRows = dt.Select(columnName + "='" + item.TemperaturePersonId + "'");
                    if (dataRows.Length.Equals(1))
                    {
                        //存在不记录上报人数
                    }
                    else
                    {
                        n = n + 1;
                    }
                }
            }
            return n;
        }
        /// <summary>
        /// 修改单条疫情汇总数据11
        /// </summary>
        /// <param name="thm"></param>
        /// <returns></returns>
        public bool Update(string id, int AddDiagnosisNum, int AddCureNum)
        {
            string sql = "update ReportOnEpidemicSituation set AddDiagnosisNum = '" + AddDiagnosisNum + "',AddCureNum='" + AddCureNum + "' where Id='"+ id + "'";
            try
            {
                db.RunNoneResultSql(sql);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public int sum(string type)
        {
            string sql = "";
            if (type == "FillInNum")
            {
                sql = "SELECT SUM(FillInNum) as c FROM ReportOnEpidemicSituation";
            }
            else if (type == "AddDiagnosisNum") {
                sql = "SELECT SUM(AddDiagnosisNum) as c FROM ReportOnEpidemicSituation";
            }
            else if (type == "AddSuspectedNum")
            {
                sql = "SELECT SUM(AddSuspectedNum) as c FROM ReportOnEpidemicSituation";
            }
            else if (type == "AddCureNum")
            {
                sql = "SELECT SUM(AddCureNum) as c FROM ReportOnEpidemicSituation";
            }

            return int.Parse(db.GetFirstData(sql).ToString());
        }
    }
}
